import pandas as pd, xlwings as xw
path=r"D:\Pyobject2023\object\Case\素材\1.01.xlsx"
sht="不规则成绩单"
df=pd.read_excel(path,"不规则成绩单")
print(df)
# 显式创建App实例
app = xw.App(visible=False) # 设置为不可见
wb = app.books.open(path)
sheetA = wb.sheets[sht]
# 查找A列的最后一行行号
end_row = None
for cell in sheetA.range('B3:B1000'):
if cell.value is None:
end_row = cell.row # 数据截止最后数据的最后一行
break
if not end_row:
raise Exception("未找到A列的最后行号")
print(end_row)
headers = sheetA.range('B3:F3').value
dataA = sheetA.range(f'B4:F{end_row}').value
print(headers)
print("***********")
print(dataA)
df = pd.DataFrame(columns=headers,
data=dataA).dropna(axis=0, how='all').dropna(axis=1, how='all')
print(df)
返回值：
| Unnamed: | 0 | Unnamed: | 1 | Unnamed: | 2 | Unnamed: | 3 | Unnamed: | 4 | Unnamed: | 5 | |
| 0 | NaN | NaN | NaN | NaN | NaN | NaN | ||||||
| 1 | NaN | 姓名 | 数学 | 语文 | 英语 | 英语 | ||||||
| 2 | NaN | 小明 | 85.5 | 67 | 84 | NaN | ||||||
| 3 | NaN | 老张 | 89 | 100 | 34 | NaN | ||||||
| 4 | NaN | 小王 | 95 | 55 | 34 | NaN | ||||||
| 5 | NaN | 小李 | 23 | 95 | NaN | NaN | ||||||
| 6 | NaN | 老六 | NaN | 43 | 99 | NaN | ||||||
| 7 | NaN | 小王 | 100 | 66 | 65 | NaN | ||||||
| 8 | NaN | 小李 | 13 | 22 | 33 | NaN | ||||||
| 11 |
['姓名', '数学', '语文', '英语', '英语']
***********
[['小明', 85.5, 67.0, 84.0, None],
['老张', 89.0, 100.0, 34.0, None],
['小王', 95.0, 55.0, 34.0, None],
['小李', 23.0, 95.0, None, None],
['老六', None, 43.0, 99.0, None],
['小王', 100.0, 66.0, 65.0, None],
['小李', 13.0, 22.0, 33.0, None],
[None, None, None, None, None]]
| 姓名 | 数学 | 语文 | 英语 | |
| 0 | 小明 | 85.5 | 67.0 | 84.0 |
| 1 | 老张 | 89.0 | 100.0 | 34.0 |
| 2 | 小王 | 95.0 | 55.0 | 34.0 |
| 3 | 小李 | 23.0 | 95.0 | NaN |
| 4 | 老六 | NaN | 43.0 | 99.0 |
| 5 | 小王 | 100.0 | 66.0 | 65.0 |
| 6 | 小李 | 13.0 | 22.0 | 33.0 |